package DAO;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.PartialResultException;

import POJO.Xerox;

public class XeroxDao 
{
	Connection c;
	
	public XeroxDao()
	{
		GenericDao gDao = new GenericDao();
		c = gDao.getConnection();
	}
	
	public void CadastrarXerox(Xerox x) throws SQLException
	{
		String sql = "INSERT INTO Xerox (dia, hora, rg, codTCC, codRevista) VALUES (?, ?, ?, ?, ?)";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, x.getDia());
		ps.setString(2, x.getHora());
		ps.setString(3, x.getRg());
		ps.setInt(4, x.getCodTCC());
		ps.setInt(5, x.getCodRevista());
		ps.execute();
		ps.close();
	}
	
	public void AlteraXerox(Xerox x) throws SQLException
	{
		String sql = "UPDATE Xerox SET dia=?, hora=?, rg=?, codTCC=?, codRevista=? WHERE diaHora=? AND rg=?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, x.getCodTCC());
		ps.setInt(2, x.getCodRevista());
		ps.setString(3, x.getDia());
		ps.setString(4, x.getHora());
		ps.setString(5, x.getRg());
		ps.execute();
		ps.close();
	}
	
	public void DeletaXerox(Xerox x) throws SQLException
	{
		String sql = "DELETE Xerox WHERE dia=? AND hora=? AND rg=?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, x.getDia());
		ps.setString(2, x.getHora());
		ps.setString(3, x.getRg());
		ps.execute();
		ps.close();
	}
	
	public String DiaAtual() throws SQLException
	{
		String dia = "";
		String sql = "SELECT CONVERT(CHAR(10), SYSDATETIME() , 103) AS dtAtual";
		PreparedStatement ps = c.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		if(rs.next())
		{
			dia = rs.getString("dtAtual");
		}
		rs.close();
		ps.close();
		return dia;
	}
	
	public String horaAtual() throws SQLException
	{
		String hora = "";
		String sql = "SELECT CONVERT(CHAR(8),GETDATE(),108) AS Hora_Exata";
		PreparedStatement ps = c.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();	
		if(rs.next())
		{
			hora = rs.getString("Hora_Exata");
		}
		rs.close();
		ps.close();
		return hora;
	}
	
	public Xerox ConsultaXerox(Xerox x) throws SQLException
	{
		String sql = "SELECT dia, hora, codTCC, codRevista, codTCC FROM Xerox WHERE rg = ?";
		
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, x.getRg());		
		ResultSet rs = ps.executeQuery();
		if(rs.next())
		{
			x.setDia(rs.getString("dia"));
			x.setHora(rs.getString("hora"));
			x.setCodTCC(rs.getInt("codTCC"));
			x.setCodRevista(rs.getInt("codRevista"));			
		}
		rs.close();
		ps.close();
		return x;
	}
	
	
	public List<Xerox> listaXerox() throws SQLException
	{
		List<Xerox> listaXerox = new ArrayList<Xerox>();
		String sql = "SELECT * FROM Xerox ORDER BY rg";
		PreparedStatement ps = c.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while(rs.next())
		{
			Xerox x = new Xerox();
			x.setDia(rs.getString("dia"));
			x.setHora(rs.getString("hora"));
			x.setRg(rs.getString("rg"));
			x.setCodRevista(rs.getInt("codRevista"));
			x.setCodTCC(rs.getInt("codTCC"));
			listaXerox.add(x);
		}
		rs.close();
		ps.close();
		return listaXerox;
	}
	
}
